/****** Object:  Stored Procedure dbo.amsp_GetWorkingContentID    Script Date: 12/10/2001 10:54:33 AM ******/
-- This procedure takes a ContentID as a parameter and makes a new content row
-- to work on if the one given is already published and has no record in a working state
-- (which includes statuses of Working, PendingApproval, or Approved, .  It does this by
-- duplicating the content record and all of the child records that reference that content
-- row, including:
--   Content_HTML
--   Content_Link
--   Content_File
--   Custom_Page_Object
--   Custom_Page_Object_Text
--   Component_Interest_Category
--   Tagged_Page_Interest_Category
--
-- Parameters:
--   IN - InContentID - The contentID of the content to be worked on
--   OUT - OutContentID - The contentID of the working record
--   IN - InCreateFlag - indicates whether or not to ALTER  a working row if the latest is
--        Published or Archived.  By default, it does ALTER  a working row if there is not one
--
-- Modification History
--   09/27/2001  N.Malhotra  Added Tagged_Page_Interest_Category to list of Tables
--   12/10/2001  N.Malhotra  Changed 3rd parameter in call to amsp_GetTableColumnList to 'Y' for
--                           Tagged_Page_Interest_Category to include InterestCategoryID in result set.
--   04/11/2002  N.Malhotra  Added Update statement for Content_Link entries that are using the old ContentID
--                           in the link itself (e.g. tagged page links).
--   04/15/2002  E.Pfleckl   Commented out the Custom_Page_Object and Custom_Page_Object_Text inserts                    
--   09/09/2003  E.Tatsui    Moved the logic to ALTER  a new record to amsp_CMCopyContentRow so that
--                           it can be used from other sps.
--
-- Copyright 2005 Advanced Solutions Intl., All Rights Reserved
CREATE             PROCEDURE amsp_CMGetWorkingContentID
    @InContentID numeric,
    @InContactID numeric,
    @OutContentID numeric OUTPUT,
    @InCreateFlag char(1) = 'Y' AS
BEGIN
  DECLARE
    @MyWorkflowStatusCode char(1),
    @ChildWorkflowStatusCode char(1),
    @ChildContentID numeric
    
  -- First, figure out if this one is working, or there is one that is working associated
  -- with this one.
  SELECT @MyWorkflowStatusCode = a.WorkflowStatusCode,
         @ChildContentID = b.ContentID,
         @ChildWorkflowStatusCode = b.WorkflowStatusCode
    FROM Content a LEFT OUTER JOIN Content b ON a.ContentID = b.PreviousContentID
   WHERE a.ContentID = @InContentID
   
  -- If the status of the contentID sent in is one of the working statuses, just return it
  IF @MyWorkflowStatusCode IN ('W','E','A')
    SET @OutContentID = @InContentID
  ELSE
    BEGIN
      -- Otherwise, if the status of the child is one of the working ones, return the child's ID
      IF @ChildWorkflowStatusCode IN ('W','E','A')
        SET @OutContentID = @ChildContentID
      ELSE
        BEGIN
          -- Otherwise, if there is a child, it must have or need a child, so recurse
          IF @ChildWorkflowStatusCode IS NOT NULL
            EXECUTE amsp_CMGetWorkingContentID @ChildContentID, @InContactID, @OutContentID
          ELSE
            -- Otherwise, the one at the end of the line is not a working one, so make a new one
            EXECUTE amsp_CMCopyContentRow @InContentID, @InContactID, @OutContentID OUTPUT
        END
    END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetWorkingContentID] TO [IMIS]
GO